<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * PrunusSecurityModel
 * 
 * @package Prunus
 * @author agosto <agosto@7dedos.com>
 * @copyright	Copyright (c) 2009-2010, 7dedos
 * @link		http://www.7dedos.com
 * @version 0.2
 * @access public
 */
class PrunusSecurityModel extends Model {
	private $ci;

	public function __construct()	{
		parent::__construct();
		$this->ci =& get_instance();
    $this->ci->load->config('PrunusSecurity');
	}

/* USUARIOS *****************************************************************************************************************/
  public function listUsers($count = FALSE, $offset = null, $limit = 10, $alive = null) {
    if ($count) {
      $sql = "select count(distinct usu_id) cuenta";
    } else {
      $sql = "select usu_id, usu_name, usu_pwdHash, usu_alive";
    }
    $sql .= " from " . $this->ci->config->item('PRUNUSSEC_tablas_usuarios');
    if ($alive !== null) $sql .= " where usu_alive=" . $alive;
    if (! $count) {
      $sql .= " order by usu_name desc";
      if ($offset !== null) $sql .= " limit " . $offset . "," .$limit;
    }
    return $this->ci->db->query($sql);
  }

  public function getUser($usuId, $usuName = null, $alive = 1) {
    $this->ci->db->select('usu_id, usu_name, usu_pwdHash, usu_alive');
  	$this->ci->db->from($this->ci->config->item('PRUNUSSEC_tablas_usuarios'));
		$this->ci->db->limit(1);
    if ($usuName !== null) $this->ci->db->where('usu_name', $usuName);
    if ($usuId !== null) $this->ci->db->where('usu_id', $usuId);
		if ($alive !== null) $this->ci->db->where('usu_alive', $alive);
		return $this->ci->db->get();
	}
  
  public function insertUser($name, $pwdHash, $alive=1) {
    $data = array('usu_name' => $name, 'usu_pwdHash' => $pwdHash, 'usu_alive' => $alive);
    $this->ci->db->insert($this->ci->config->item('PRUNUSSEC_tablas_usuarios'), $data); 
    return ($this->ci->db->insert_id());
  }
  
  public function deleteUser($usuId) {
    $data = array('usu_alive' => 0);
		$this->ci->db->where('usu_id', $usuId);
    $this->ci->db->update($this->ci->config->item('PRUNUSSEC_tablas_usuarios'), $data); 
  }
  
  public function killUser($usuId) {
    $this->emptyPermisos($usuId, null, null);
    $this->emptyUserRoles($usuId, null);
		$this->ci->db->where('usu_id', $usuId);
    $this->ci->db->delete($this->ci->config->item('PRUNUSSEC_tablas_usuarios')); 
  }
  
  public function updateUser($usuId, $data, $filters=null) {
    foreach($data as $campo=>$valor) $this->ci->db->set($campo, $valor);
    if ($filters !== null) {
      if (is_array($filters)) foreach($filters as $campo=>$filtro) $this->ci->db->where($campo, $filtro);
      if (is_string($filters)) $this->ci->db->where($filters);
    } else {
      $this->ci->db->where('usu_id', $usuId);
    }
    return ($this->ci->db->update($this->ci->config->item('PRUNUSSEC_tablas_usuarios')));
  }
  
  /* usuarios-roles */
  public function insertUserRole($usuId,$rolId) {
    $data = array('usurol_usu' => $usuId, 'usurol_rol' => $rolId);
    $this->ci->db->insert($this->ci->config->item('PRUNUSSEC_tablas_usuarios_roles'), $data); 
    return ($this->ci->db->insert_id());
  }
  
  public function getUsuRoles($usuId=0, $exclusionArray=null) {
    if ($usuId === 0) $usuId = $this->ci->prunuslib->usuId;
    $sql =  "SELECT rol_id,rol_name,rol_builtin";
    $sql .= " FROM " . $this->ci->config->item('PRUNUSSEC_tablas_usuarios_roles') . " inner join " . $this->ci->config->item('PRUNUSSEC_tablas_roles') . " on usurol_rol=rol_id and rol_alive=1";
    $sql .= " where rol_alive=1";
    $sql .= " and usurol_usu=" . $usuId;
    if ($exclusionArray !== null) {
      $exclusionList = implode(",", $exclusionArray);
      if (strlen($exclusionList) > 0) $sql .= " and rol_id not in (" . $exclusionList . ")";
    }
    $sql .= " order by rol_id";
    return $this->ci->db->query($sql);    
  }
  
  public function emptyUserRoles($usuId = null, $rolId = null) {
		$this->ci->db->where('usurol_usu', $usuId);
    $this->ci->db->delete($this->ci->config->item('PRUNUSSEC_tablas_usuarios_roles')); 
  }
/***************************************************************************************************************** USUARIOS */






/* ROLES *******************************************************************/
  public function listRoles($count = FALSE, $offset = null, $limit = 10, $alive = null, $exclusionArray=null) {
    if ($count) {
      $sql = "select count(distinct rol_id) cuenta";
    } else {
      $sql = "select rol_id, rol_name, rol_builtin, rol_alive";
    }
    $sql .= " from " . $this->ci->config->item('PRUNUSSEC_tablas_roles');
    $sql .= " where 1=1";
    if ($alive !== null) $sql .= " and rol_alive=" . $alive;
    if ($exclusionArray !== null) {
      $exclusionList = implode(",", $exclusionArray);
      if (strlen($exclusionList) > 0) $sql .= " and rol_id not in (" . $exclusionList . ")";
    }
    if (! $count) {
      $sql .= " order by rol_name desc";
      if ($offset !== null) $sql .= " limit " . $offset . "," .$limit;
    }
    return $this->ci->db->query($sql);
  }

  
  public function getRole($rolId, $alive = 1) {
    $this->ci->db->select('rol_id, rol_name, rol_builtin, rol_alive');
  	$this->ci->db->from($this->ci->config->item('PRUNUSSEC_tablas_roles'));
		$this->ci->db->limit(1);
    $this->ci->db->where('rol_id', $rolId);
		if ($alive !== null) $this->ci->db->where('rol_alive', $alive);
		return $this->ci->db->get();
	}
  
  public function insertRole($name, $alive=1) {
    $data = array('rol_name' => $name, 'rol_builtin' => 0, 'rol_alive' => $alive);
    $this->ci->db->insert($this->ci->config->item('PRUNUSSEC_tablas_roles'), $data); 
    return ($this->ci->db->insert_id());
  }
  
  public function deleteRole($rolId) {
    $data = array('rol_alive' => 0);
		$this->ci->db->where('rol_id', $usuId);
    $this->ci->db->update($this->ci->config->item('PRUNUSSEC_tablas_roles'), $data); 
  }
  
  public function killRole($rolId) {
    $this->emptyPermisos(null, $rolId, null);
    $this->emptyUserRoles(null, $rolId);
		$this->ci->db->where('rol_id', $rolId);
    $this->ci->db->delete($this->ci->config->item('PRUNUSSEC_tablas_roles')); 
  }
  
  public function updateRole($rolId, $data, $filters=null) {
    foreach($data as $campo=>$valor) $this->ci->db->set($campo, $valor);
    if ($filters !== null) {
      if (is_array($filters)) foreach($filters as $campo=>$filtro) $this->ci->db->where($campo, $filtro);
      if (is_string($filters)) $this->ci->db->where($filters);
    } else {
      $this->ci->db->where('rol_id', $rolId);
    }
    return ($this->ci->db->update($this->ci->config->item('PRUNUSSEC_tablas_roles')));
  }
  
/***************************************************************************/





/* PAGES *******************************************************************/
  public function listPages($count = FALSE, $offset = null, $limit = 10, $alive = null) {
    if ($count) {
      $sql = "select count(distinct pag_id) cuenta";
    } else {
      $sql = "select pag_id, pag_identifier, pag_title, pag_public, pag_alive";
    }
    $sql .= " from " . $this->ci->config->item('PRUNUSSEC_tablas_paginas');
    if ($alive !== null) $sql .= " where pag_alive=" . $alive;
    if (! $count) {
      $sql .= " order by pag_identifier desc";
      if ($offset !== null) $sql .= " limit " . $offset . "," .$limit;
    }
    return $this->ci->db->query($sql);
  }
  
  public function getPage($pagId, $pagIdentifier = null, $alive = 1) {
    $this->ci->db->select('pag_id, pag_identifier, pag_title, pag_public, pag_alive');
  	$this->ci->db->from($this->ci->config->item('PRUNUSSEC_tablas_paginas'));
		$this->ci->db->limit(1);
    if ($pagIdentifier !== null) $this->ci->db->where('pag_identifier', $pagIdentifier);
    if ($pagId !== null) $this->ci->db->where('pag_id', $pagId);
		if ($alive !== null) $this->ci->db->where('pag_alive', $alive);
		return $this->ci->db->get();
	}
  
  public function insertPage($identifier, $title, $public=1, $alive=1) {
    $data = array('pag_identifier' => $identifier, 'pag_title' => $title, 'pag_public' => $public, 'pag_alive' => $alive);
    $this->ci->db->insert($this->ci->config->item('PRUNUSSEC_tablas_paginas'), $data); 
    return ($this->ci->db->insert_id());
  }
  
  public function deletePage($pagId) {
    $data = array('pag_alive' => 0);
		$this->ci->db->where('pag_id', $pagId);
    $this->ci->db->update($this->ci->config->item('PRUNUSSEC_tablas_paginas'), $data); 
  }
  
  public function killPage($pagId) {
    $this->emptyPermisos(null, null, $pagId);
		$this->ci->db->where('pag_id', $pagId);
    $this->ci->db->delete($this->ci->config->item('PRUNUSSEC_tablas_paginas')); 
  }
  
  public function updatePage($pagId, $data, $filters=null) {
    foreach($data as $campo=>$valor) $this->ci->db->set($campo, $valor);
    if ($filters !== null) {
      if (is_array($filters)) foreach($filters as $campo=>$filtro) $this->ci->db->where($campo, $filtro);
      if (is_string($filters)) $this->ci->db->where($filters);
    } else {
      $this->ci->db->where('pag_id', $pagId);
    }
    return ($this->ci->db->update($this->ci->config->item('PRUNUSSEC_tablas_paginas')));
  }
  
/***************************************************************************/
  

/* PERMISOS ****************************************************************/
  public function listPermisos($count = FALSE, $offset = null, $limit = 10, $alive = null) {
    if ($count) {
      $sql = "select count(distinct per_id) cuenta";
    } else {
      $sql = "select pag_id, pag_identifier, pag_title, pag_public, pag_alive";
    }
    $sql .= " from " . $this->ci->config->item('PRUNUSSEC_tablas_permisos');
    if ($alive !== null) $sql .= " where pag_alive=" . $alive;
    if (! $count) {
      $sql .= " order by pag_identifier desc";
      if ($offset !== null) $sql .= " limit " . $offset . "," .$limit;
    }
    return $this->ci->db->query($sql);
  }
  
  public function getPermiso($perId, $perIdentifier = null, $alive = 1) {
    $this->ci->db->select('pag_id, pag_identifier, pag_title, pag_public, pag_alive');
  	$this->ci->db->from($this->ci->config->item('PRUNUSSEC_tablas_paginas'));
		$this->ci->db->limit(1);
    if ($perIdentifier !== null) $this->ci->db->where('pag_identifier', $perIdentifier);
    if ($perId !== null) $this->ci->db->where('pag_id', $perId);
		if ($alive !== null) $this->ci->db->where('pag_alive', $alive);
		return $this->ci->db->get();
	}
  
  public function insertPermiso($identifier, $title, $public=1, $alive=1) {
    $data = array('pag_identifier' => $identifier, 'pag_title' => $title, 'pag_public' => $public, 'pag_alive' => $alive);
    $this->ci->db->insert($this->ci->config->item('PRUNUSSEC_tablas_paginas'), $data); 
    return ($this->ci->db->insert_id());
  }
  
  public function deletePermiso($perId) {
    $data = array('pag_alive' => 0);
		$this->ci->db->where('pag_id', $perId);
    $this->ci->db->update($this->ci->config->item('PRUNUSSEC_tablas_paginas'), $data); 
  }
  
  public function killPermiso($perId) {
    $this->emptyPermisos(null, null, $perId);
		$this->ci->db->where('pag_id', $perId);
    $this->ci->db->delete($this->ci->config->item('PRUNUSSEC_tablas_paginas')); 
  }
  
  public function updatePermiso($perId, $data, $filters=null) {
    foreach($data as $campo=>$valor) $this->ci->db->set($campo, $valor);
    if ($filters !== null) {
      if (is_array($filters)) foreach($filters as $campo=>$filtro) $this->ci->db->where($campo, $filtro);
      if (is_string($filters)) $this->ci->db->where($filters);
    } else {
      $this->ci->db->where('pag_id', $perId);
    }
    return ($this->ci->db->update($this->ci->config->item('PRUNUSSEC_tablas_paginas')));
  }

  public function emptyPermisos($usuId = null, $rolId = null, $pagId = null) {
		if ($usuId !== null) $this->ci->db->where('per_usu', $usuId);
		if ($rolId !== null) $this->ci->db->where('per_rol', $rolId);
		if ($pagId !== null) $this->ci->db->where('per_pag', $pagId);
    if ($usuId !== null || $rolId !== null || $pagId !== null) $this->ci->db->delete($this->ci->config->item('PRUNUSSEC_tablas_permisos')); 
  }

  public function getUsuPermisosPage($usuId, $pagId, $rolesList = array()) {
    $sql  = "select";
    $sql .= " sum(case when per_usu = 0 and per_permiso<0 then 1 else 0 end) per_rol_neg,";
    $sql .= " sum(case when per_usu = 0 and per_permiso=0 then 1 else 0 end) per_rol_nul,";
    $sql .= " sum(case when per_usu = 0 and per_permiso>0 then 1 else 0 end) per_rol_pos,";
    $sql .= " sum(case when per_rol = 0 and per_permiso<0 then 1 else 0 end) per_usu_neg,";
    $sql .= " sum(case when per_rol = 0 and per_permiso=0 then 1 else 0 end) per_usu_nul,";
    $sql .= " sum(case when per_rol = 0 and per_permiso>0 then 1 else 0 end) per_usu_pos";
    $sql .= " FROM pr_permisos";
    $sql .= " where per_pag=" . $pagId;
    if (count($rolesList) > 0) $sql .= " and (per_usu=" . $usuId . " or per_rol in (" . implode(",", $rolesList) . "))";
    return $this->ci->db->query($sql);
  }

  
/***************************************************************************/


}
